CREATE TABLE [dbo].[tb_AlumnoDivision]
(
[idRelDivisionAlumno] [int] NOT NULL IDENTITY(1, 1),
[Alumno] [int] NULL,
[Division] [int] NULL,
[AnioLectivo] [int] NOT NULL,
[Desde] [datetime] NOT NULL,
[Hasta] [datetime] NULL,
[PaseDeOrigen] [int] NULL,
[InscripcionDeOrigen] [int] NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CausaBaja] [int] NULL,
[cargo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hora] [datetime] NULL,
[previas] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[DivisionesDuplicadas] ON [dbo].[tb_AlumnoDivision] 
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @alumno INT
declare @division int
declare @aniolectivo int

SELECT  @alumno = alumno,@division = division, @aniolectivo = aniolectivo FROM Inserted 

if (select count(*) from tb_alumnodivision where @alumno=alumno and @division = division and @aniolectivo = aniolectivo) >1
--
 BEGIN 
	ROLLBACK transaction 
	RAISERROR('El Alumno  ya se encuentra matriculado en la división seleccionada', 16,1)
          
END
GO
DISABLE TRIGGER [dbo].[DivisionesDuplicadas] ON [dbo].[tb_AlumnoDivision]
GO
ALTER TABLE [dbo].[tb_AlumnoDivision] ADD CONSTRAINT [PK_tb_AlumnoDivision] PRIMARY KEY NONCLUSTERED  ([idRelDivisionAlumno]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Alumno] ON [dbo].[tb_AlumnoDivision] ([Alumno]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_tb_RelAlumnoDivisionç] ON [dbo].[tb_AlumnoDivision] ([idRelDivisionAlumno]) WITH (FILLFACTOR=98) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_AlumnoDivision] WITH NOCHECK ADD CONSTRAINT [FK_tb_AlumnoDivision_tb_Alumnos] FOREIGN KEY ([Alumno]) REFERENCES [dbo].[tb_Alumnos] ([idAlumno])
GO
ALTER TABLE [dbo].[tb_AlumnoDivision] WITH NOCHECK ADD CONSTRAINT [FK_tb_AlumnoDivision_tb_CausaBajaAlumno1] FOREIGN KEY ([CausaBaja]) REFERENCES [dbo].[tb_CausaBajaAlumno] ([IdCausa]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_AlumnoDivision] WITH NOCHECK ADD CONSTRAINT [FK_tb_AlumnoDivision_tb_Divisionestemp] FOREIGN KEY ([Division]) REFERENCES [dbo].[tb_Divisionestemp] ([idDivision])
GO
ALTER TABLE [dbo].[tb_AlumnoDivision] NOCHECK CONSTRAINT [FK_tb_AlumnoDivision_tb_CausaBajaAlumno1]
GO
GRANT SELECT ON  [dbo].[tb_AlumnoDivision] TO [Coordinaciones]
GRANT INSERT ON  [dbo].[tb_AlumnoDivision] TO [Coordinaciones]
GRANT UPDATE ON  [dbo].[tb_AlumnoDivision] TO [Coordinaciones]
GO
